import string

from flask import request, jsonify, Blueprint

from app import db
from models import AmzRanking, RankingTable, AmzSupervisoryControl, AmzContent
from flask import Flask, request, jsonify, send_file
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment
from io import BytesIO
import string
import datetime

rankings_bp = Blueprint('rankings', __name__)


# 获取排行榜数据并进行分页
@rankings_bp.route('/api/rankings', methods=['GET'])
def get_rankings():
    # 从请求中获取页码（默认为第一页）
    page = request.args.get('page', 1, type=int)
    per_page = request.args.get('per_page', 10, type=int)  # 每页显示的记录数，默认为10

    # 查询数据并进行分页
    rankings = AmzRanking.query.paginate(page=page, per_page=per_page, error_out=False)

    # 返回分页结果
    return jsonify({
        'page': rankings.page,
        'per_page': rankings.per_page,
        'total': rankings.total,
        'total_pages': rankings.pages,
        'rankings': [
            {"id": r.id, "name": r.name, "url": r.url, "status": r.status, "type": r.type} for r in rankings.items
        ]
    })


@rankings_bp.route('/api/ranking_table', methods=['GET'])
def get_ranking_table():
    # 从请求中获取页码（默认为第一页）
    page = request.args.get('page', 1, type=int)
    per_page = request.args.get('per_page', 10, type=int)  # 每页显示的记录数，默认为10
    name = request.args.get('name', None)  # 获取模糊查询的 name 参数

    # 构建基础查询
    query = RankingTable.query

    # 如果传入了 name，添加模糊查询条件
    if name:
        query = query.filter(RankingTable.name.like(f'%{name}%'))  # LIKE 模糊匹配（区分大小写）

    # 执行分页查询
    rankings = query.paginate(page=page, per_page=per_page, error_out=False)

    # 返回分页结果
    return jsonify({
        'page': rankings.page,
        'per_page': rankings.per_page,
        'total': rankings.total,
        'total_pages': rankings.pages,
        'rankings': [
            {"id": r.id, "name": r.name, "url": r.url, "pate_id": r.pate_id, "status": r.status} for r in rankings.items
        ]
    })


@rankings_bp.route('/api/edit/ranking_table/<int:id>', methods=['PUT'])
def update_ranking_status(id):
    # 获取请求体中的JSON数据
    data = request.get_json()
    new_status = data.get('status')

    if new_status == 0:
        pass
    elif not new_status:
        return jsonify({"error": "Status is required"}), 400
    else:
        pass

    # 查询记录
    record = RankingTable.query.get(id)
    if not record:
        return jsonify({"error": "Record not found"}), 404

    # 更新状态
    try:
        record.status = new_status
        db.session.commit()
        return jsonify({
            "success": True,
            "message": "Status updated successfully",
            "data": {
                "id": record.id,
                "new_status": record.status
            }
        })
    except Exception as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 500


@rankings_bp.route('/api/amz_supervisory_control', methods=['GET'])
def get_ranking_two_table():
    # 从请求中获取页码（默认为第一页）
    page = request.args.get('page', 1, type=int)
    per_page = request.args.get('per_page', 10, type=int)  # 每页显示的记录数，默认为10
    asin = request.args.get('ASIN', None)  # 获取ASIN查询参数

    # 构建基础查询
    query = AmzSupervisoryControl.query

    # 如果提供了ASIN参数，添加模糊查询条件
    if asin:
        query = query.filter(AmzSupervisoryControl.ASIN.like(f'%{asin}%'))  # 模糊匹配ASIN字段

    # 执行分页查询
    rankings = query.paginate(page=page, per_page=per_page, error_out=False)

    # 返回分页结果
    return jsonify({
        'page': rankings.page,
        'per_page': rankings.per_page,
        'total': rankings.total,
        'total_pages': rankings.pages,
        'rankings': [
            {"id": r.id, "ASIN": r.ASIN, "brand_name": r.brand_name, "ranking": r.ranking,
             "fba_json": r.fba_json, "type": r.type, "create_time": r.create_time, } for r in rankings.items
        ]
    })


@rankings_bp.route('/api/amz_content', methods=['GET'])
def get_amz_content():
    # 从请求中获取页码（默认为第一页）
    page = request.args.get('page', 1, type=int)
    per_page = request.args.get('per_page', 10, type=int)  # 每页显示的记录数，默认为10
    asin = request.args.get('ASIN', None)  # 获取ASIN查询参数

    # 构建基础查询
    query = AmzContent.query

    # 如果提供了ASIN参数，添加模糊查询条件
    if asin:
        query = query.filter(AmzContent.ASIN.like(f'%{asin}%'))  # 模糊匹配ASIN字段

    # 执行分页查询
    rankings = query.paginate(page=page, per_page=per_page, error_out=False)

    # 返回分页结果
    return jsonify({
        'page': rankings.page,
        'per_page': rankings.per_page,
        'total': rankings.total,
        'total_pages': rankings.pages,
        'rankings': [
            {"id": r.id, "ASIN": r.ASIN, "brand_name": r.brand_name, "ranking": r.ranking,
             "fba_json": r.fba_json, "type": r.type, "create_time": r.create_time, "rank_id": r.rank_id} for r in
            rankings.items
        ]
    })


# 导出

@rankings_bp.route('/api/export/amz_content', methods=['GET'])
def expect_export_amz_content():
    page = request.args.get('page', 1, type=int)
    per_page = request.args.get('per_page', 10, type=int)  # 每页显示的记录数，默认为10
    asin = request.args.get('ASIN', None)  # 获取ASIN查询参数

    # 构建基础查询
    query = AmzContent.query

    # 如果提供了ASIN参数，添加模糊查询条件
    if asin:
        query = query.filter(AmzContent.ASIN.like(f'%{asin}%'))  # 模糊匹配ASIN字段

    # 执行分页查询
    rankings = query.paginate(page=page, per_page=per_page, error_out=False)
    export_list = []
    tableName = "产品详情表"
    titleData = [
        '序号', "ASIN", '品牌名称', "排名数据", '排行榜数据', '类型', '排行榜链接id',
    ]
    data_list = []
    index = 0
    for items in [
        {"id": r.id, "ASIN": r.ASIN, "brand_name": r.brand_name, "ranking": r.ranking,
         "fba_json": r.fba_json, "type": r.type, "rank_id": r.rank_id} for r in
        rankings.items
    ]:
        index = index + 1
        li = [
            index,
            items['ASIN'],
            items['brand_name'],
            items['ranking'],
            items['fba_json'],
            items['type'],
            items['rank_id'],
        ]
        data_list.append(li)
    export_list.append({"tableName": tableName,
                        "titleData": titleData,
                        "data_list": data_list})
    return common_export_multiple_sheet_excel(export_list)


@rankings_bp.route('/api/export/amz_supervisory_control', methods=['GET'])
def expect_export_amz_supervisory_control():
    page = request.args.get('page', 1, type=int)
    per_page = request.args.get('per_page', 10, type=int)  # 每页显示的记录数，默认为10
    asin = request.args.get('ASIN', None)  # 获取ASIN查询参数

    # 构建基础查询
    query = AmzSupervisoryControl.query

    # 如果提供了ASIN参数，添加模糊查询条件
    if asin:
        query = query.filter(AmzSupervisoryControl.ASIN.like(f'%{asin}%'))  # 模糊匹配ASIN字段

    # 执行分页查询
    rankings = query.paginate(page=page, per_page=per_page, error_out=False)
    export_list = []
    tableName = "产品监控表"
    titleData = [
        '序号', "ASIN", '品牌名字', "排名数据", '排行榜数据', '类型',
    ]
    data_list = []
    index = 0
    for items in [
        {"id": r.id, "ASIN": r.ASIN, "brand_name": r.brand_name, "ranking": r.ranking,
         "fba_json": r.fba_json, "type": r.type} for r in rankings.items
    ]:
        index = index + 1
        li = [
            index,
            items['ASIN'],
            items['brand_name'],
            items['ranking'],
            items['fba_json'],
            items['type'],
        ]
        data_list.append(li)
    export_list.append({"tableName": tableName,
                        "titleData": titleData,
                        "data_list": data_list})
    return common_export_multiple_sheet_excel(export_list)


def common_export_multiple_sheet_excel(export_list):
    column_dimensions = list(string.ascii_uppercase)
    wb = Workbook()
    ws = wb.active

    try:
        tableName = ''
        for inx, i in enumerate(export_list):
            tableName = i['tableName']
            titleData = i['titleData']
            data_list = i['data_list']

            fontObj1 = Font(name=u'微软雅黑', bold=True, size=12)
            aligmentCenter = Alignment(wrapText=True, horizontal='center', vertical='center')

            ws = wb.create_sheet(tableName, inx)

            for row in ws['a1:z1']:
                for cell in row:
                    cell.font = fontObj1

            column_num = len(titleData)
            ws_col = column_dimensions[:column_num]
            for i in ws_col:
                ws.column_dimensions[i].width = 40.0

            data_list.insert(0, titleData)
            for datas in data_list:
                for i, data in zip(range(len(datas)), datas):
                    if isinstance(data, dict):
                        data = str(data)  # 需要转换字典为字符串
                        datas[i] = data

            r = 1
            for line in data_list:
                for col in range(1, len(line) + 1):
                    ws.cell(row=r, column=col).value = line[col - 1]
                    ws.cell(row=r, column=col).alignment = aligmentCenter
                r += 1

        # 保存到内存流
        output = BytesIO()
        wb.save(output)
        output.seek(0)

        # 返回文件作为响应
        filename = tableName + str(datetime.datetime.now())
        return send_file(output, as_attachment=True, download_name=f"{filename}.xlsx",
                         mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")

    except Exception as e:
        # 错误处理
        return jsonify({'error': str(e)}), 500
